const { Client } = require('pg');

// 创建 PostgreSQL 客户端实例
const client = new Client({
    user: 'postgres',
    password: 'Heromy@1322',
    host: 'localhost',
    port: 5432, // 默认 PostgreSQL 端口号为 5432
    database: 'my'
});

client.connect((err) => {
    if (err) {
        console.error('Error connecting to PostgreSQL database: ', err);
        return;
    }
    console.log('Connected to PostgreSQL database');

    // 创建 users 表
    const createTableQuery = `CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        age INT NOT NULL
    )`;

    client.query(createTableQuery, (err, result) => {
        if (err) {
            console.error('Error creating users table: ', err);
            return;
        }
        console.log('Users table created successfully');

        // 添加数据到 users 表
        const insertDataQuery = `INSERT INTO users (name, age) VALUES ($1, $2)`;
        const usersData = [
            ['John Doe', 25],
            ['Jane Smith', 30],
            ['Mike Johnson', 27],
            ['Emily Young', 28],
            ['David Lee', 24]
        ];

        usersData.forEach((data) => {
            client.query(insertDataQuery, data, (err, result) => {
                if (err) {
                    console.error('Error inserting user data: ', err);
                    return;
                }
                console.log('User data inserted successfully');
            });
        });

        // 删除数据
        /*const deleteDataQuery = `DELETE FROM users WHERE id = $1`;
        client.query(deleteDataQuery, [1], (err, result) => {
            if (err) {
                console.error('Error deleting user: ', err);
                return;
            }
            console.log('User deleted successfully');
        });*/

        // 更新数据
        /*const updateData = { name: 'Jane Doe', age: 30 };
        const updateDataQuery = `UPDATE users SET name = $1, age = $2 WHERE id = $3`;
        client.query(updateDataQuery, [updateData.name, updateData.age, 2], (err, result) => {
            if (err) {
                console.error('Error updating user: ', err);
                return;
            }
            console.log('User updated successfully');
        });*/

        // 读取数据
        const selectDataQuery = `SELECT * FROM users`;
        client.query(selectDataQuery, (err, results) => {
            if (err) {
                console.error('Error reading users: ', err);
                return;
            }
            console.log('Users:', results.rows);
        });

        // client.end(); // 关闭数据库连接
    });
});